#region imported Namespaces
using System;
using System.Data;
using SqlTypes = System.Data.SqlTypes;
using System.Collections.Specialized;
using System.Data.SqlClient;
using BO = PHSRAG.Insight21.BusinessObjects;
using Utility = PHSRAG.Utility;
using ICachePolicy = PHSRAG.Insight21.Policy.ICachePolicy;
using UserContext = PHSRAG.Insight21.Context.UserContext;
using AccessRightBit = PHSRAG.Security.Enumerations.AccessRightBit;
using UserOrderType = PHSRAG.Insight21.Purchasing.UserOrderUtility.UserOrderType;
#endregion

namespace PHSRAG.Insight21.Purchasing
{
	#region User Order Class
	/// <summary>
	/// Base class for all the User Orders. It encapsulates the property and
	/// behaviour common to all types of user order. It supports viewing, adding,
	/// editing and deleting an user order.
	/// </summary>
	public abstract class UserOrder: BO.SaveableBusinessObject
	{
		#region Constants
		private const string UserOrderKey					= "UserOrderKey_5d3b569f-5a29-4e36-bf35-80b7ae9a5c5b";
		protected const int FirstDataTableIndex				= 0;
		private const int DefaultId							= 0;

		//Stored Procedure Parameter Name
		private const string DomainUserIDParamName			= "@domainUserID";
		private const string OrderIDParamName				= "@orderID";
		private const string OrderTypeIDParamName			= "@orderTypeID";
		private const string OrderNumberParamName			= "@orderNumber";
		private const string TransactionDateParamName		= "@transactionDate";
		private const string TotalAmountParamName			= "@totalAmount";
		private const string CommentParamName				= "@comment";
        private const string InvoiceNumberParamName         = "@invoiceNumber";
        private const string InvoiceDateParamName           = "@invoiceDate";

		//Data Column Names
		private const string OrderIDColumnName				= "OrderId";
		private const string OrderTypeIDColumnName			= "OrderTypeID";
		private const string OrderNumberColumnName			= "OrderNumber";
		private const string TransactionDateColumnName		= "TransactionDate";
		private const string TotalAmountColumnName			= "TotalAmount";
		private const string CommentColumnName				= "Comment";
		private const string AccessLevelColumnName			= "AccessLevel";
        private const string InvoiceNumberColumnName        = "InvoiceNumber";
        private const string InvoiceDateColumnName          = "InvoiceDate";

		//Stored Procedure Name
		private const string GetUserOrderHeaderSPName		= "GetUserOrderHeader";
		private const string DeleteUserOrderSPName			= "DeleteUserOrder";
		private const string DeleteReconcileOrderSPName		= "DeleteReconcileOrder";
		#endregion

		#region Enumerations (Private)

		// Enumerates the arguments required
		// for loading user order header.
		private enum LoadHeaderSPArguments
		{
			DomainUserID,
			OrderID,
            OrderTypeID,
			Count
		}

		#endregion
		
		#region Instance Variables
		private UserOrderLineItems lineItems;
		protected SqlCommand addCommand;
		protected SqlCommand updateCommand;		
		// Is used to check if the command object
		// for current user order has been created.
		protected bool isCommandCreated = false;
		
		#endregion

		#region Instance Properties (Public)
		/// <summary>
		/// Gets and sets the OrderID. Returns zero
		/// if value in datatable is DBNull.Value
		/// </summary>
		public int OrderID
		{
			get
			{
				object orderID = DataContainer.Tables[FirstDataTableIndex].Rows[0][OrderIDColumnName];
				return (orderID == DBNull.Value) ? DefaultId : (int)orderID;
			}
            set { DataContainer.Tables[FirstDataTableIndex].Rows[0][OrderIDColumnName] = value; }
		}
		/// <summary>
		/// Gets and sets the OrderTypeID. Returns zero
		/// if value in datatable is DBNull.Value
		/// </summary>
		public int OrderTypeID
		{
			get
			{
				object orderTypeID = DataContainer.Tables[FirstDataTableIndex].Rows[0][OrderTypeIDColumnName];
				return (orderTypeID == DBNull.Value) ? (int)UserOrderType.PurchaseOrder : (int)orderTypeID;
			}
            set { DataContainer.Tables[FirstDataTableIndex].Rows[0][OrderTypeIDColumnName] = value; }
		}
		/// <summary>
		/// Gets and sets the OrderNumber. Returns an 
		/// empty string if value in datatable is DBNull.Value
		/// </summary>
		public string OrderNumber
		{
			get
			{
				object orderNumber = DataContainer.Tables[FirstDataTableIndex].Rows[0][OrderNumberColumnName];
				return (orderNumber == DBNull.Value) ? string.Empty : orderNumber.ToString();
			}
			set{DataContainer.Tables[FirstDataTableIndex].Rows[0][OrderNumberColumnName] = value;}
		}
		/// <summary>
		/// Gets and sets the TransactionDate. Returns an empty string if value 
		/// in datatable is DBNull.Value or empty string otherwise a string representing
		/// the date values in "MM/dd/yyyy" format
		/// </summary>
		public string TransactionDate
		{
			get
			{
				object transactionDate = DataContainer.Tables[FirstDataTableIndex].Rows[0][TransactionDateColumnName];
				return (transactionDate == DBNull.Value || transactionDate.ToString().Equals(string.Empty)) ?
					string.Empty : Convert.ToDateTime(transactionDate).ToString("MM/dd/yyyy");
			}
            set { DataContainer.Tables[FirstDataTableIndex].Rows[0][TransactionDateColumnName] = value; }
		}
		/// <summary>
		/// Gets the sum of line items amount
		/// </summary>
		public decimal TotalAmount
		{
			get{return GetTotalAmount();}
		}
		/// <summary>
		/// Gets and sets the Comment. Returns an empty
		/// string if value in datatable is DBNull.Value
		/// </summary>
		public string Comment
		{
			get
			{
				object comment = DataContainer.Tables[FirstDataTableIndex].Rows[0][CommentColumnName];
				return (comment == DBNull.Value) ? string.Empty : comment.ToString();
			}
            set { DataContainer.Tables[FirstDataTableIndex].Rows[0][CommentColumnName] = value; }
		}

        /// <summary>
        /// Gets and sets the InvoiceNumber. Returns empty string if value in datatable is DBNull.Value.
        /// </summary>
        public string InvoiceNumber
        {
            get
            {
                object invoiceNumber = DataContainer.Tables[FirstDataTableIndex].Rows[0][InvoiceNumberColumnName];
                return (invoiceNumber == DBNull.Value) ? string.Empty : invoiceNumber.ToString();
            }
            set { DataContainer.Tables[FirstDataTableIndex].Rows[0][InvoiceNumberColumnName] = value; }
        }

        /// <summary>
        /// Gets and sets the InvoiceDate. Returns an empty string if value in datatable is DBNull.Value
        /// or empty string otherwise a string representing the date value in "MM/dd/yyyy" format.
        /// </summary>
        public string InvoiceDate
        {
            get
            {
                object invoiceDate = DataContainer.Tables[FirstDataTableIndex].Rows[0][InvoiceDateColumnName];
                return (invoiceDate == DBNull.Value || invoiceDate.ToString().Equals(string.Empty)) ?
                    string.Empty : Convert.ToDateTime(invoiceDate).ToString("MM/dd/yyyy");
            }
            set
            {
                DataContainer.Tables[FirstDataTableIndex].Rows[0][InvoiceDateColumnName] = value.Equals(string.Empty) ?
                    (object)DBNull.Value : (object)value;
            }
        }

		#region Public contained object
		/// <summary>
		/// This property return an instance of UserOrderLineItems.
		/// It checks if the instance variable lineItems is null. If yes it instantiates
		/// the lineitems and loads the collection with data if orderid is more than zero,
		/// else adds a line item to the collection and returns the lineitems collection.
		/// </summary>
		public UserOrderLineItems LineItems
		{
			get
			{			
				if(lineItems == null)
				{
					lineItems = new UserOrderLineItems(DataContainer, CachePolicy);
					if(OrderID > DefaultId)
					{
						lineItems.Load(Utility.DataAccess.GetConnectionString(),OrderID,
							((UserContext)CachePolicy.GetCachedObject(UserContext.CacheKey)).DomainUserId);
					}
					else
						lineItems.CreateLineItem();
				}
				return lineItems;
			}			
		}
		#endregion

		#endregion		

		#region Class properties
		/// <summary>
		/// Get the key to be used in all caching operations for this instance of the object.
		/// </summary>
		public static string CacheKey	{ get { return UserOrderKey; } }
		#endregion

		#region Constructor
		
		/// <summary>
		/// Initializes the base class with the specified cache policy
		/// </summary>
		/// <param name="cachePolicy">prevalent cache policy</param>
		public UserOrder(ICachePolicy cachePolicy) : base(cachePolicy)
		{			
		}
		
		#endregion

		#region Public Methods
		/// <summary>
		/// Assigns values to the member variables of the current instance of user order. 
		/// This method checks for the self assignment and then goes on to assign properties
		/// common to all the user order types. It is is called by the overridden methods 
		/// in the child classes.
		/// </summary>
		/// <param name="baseUserOrder">The exisiting user order object</param>
		public virtual void AssignProperties(UserOrder userOrder, UserOrderType orderType)
		{
			if(!this.Equals(userOrder))
			{
				OrderID			=	userOrder.OrderID;
				OrderNumber		=	userOrder.OrderNumber;
				OrderTypeID		=	(int)orderType;
				TransactionDate =	userOrder.TransactionDate;
				Comment			=	userOrder.Comment;				
				lineItems		=	userOrder.LineItems;				
			}
		}

		/// <summary>
		/// Initializes the datacontainer and adds a table for user order header to it.
		/// This method is called by the overridden methods in the child classes to add
		/// the columns common to all the user order types to header table.
		/// The datacontainer of this class is initialized only once, either in this 
		/// method or the Load method. This method is called only when a new user order
		/// is created and in that case Load method on the class is not called. This method
		/// is necessary because the properties of this class have been bound to records
		/// contained in a table of the DataConatiner. In situation when new user order is created,
		/// and its property accessed to get or set, it will throw an error unless its dataConatiner
		/// is not intialized and it does not have the header table with one row.
		/// </summary>
		public virtual void CreateHeaderTableAndAddRow()
		{
			DataContainer = new DataSet();
			DataTable orderHeader = new DataTable(Constants.UserOrderDetailsHeaderTableName);
			DataContainer.Tables.Add(orderHeader);
			
			orderHeader.Columns.Add(OrderIDColumnName, typeof(int));
			orderHeader.Columns.Add(OrderTypeIDColumnName, typeof(int));
			orderHeader.Columns.Add(OrderNumberColumnName, typeof(string));
			orderHeader.Columns.Add(TransactionDateColumnName, typeof(string));
			orderHeader.Columns.Add(TotalAmountColumnName, typeof(decimal));
			orderHeader.Columns.Add(CommentColumnName, typeof(string));
			orderHeader.Columns.Add(AccessLevelColumnName, typeof(int));
		}

		/// <summary>
		/// Deletes the user order from the Reconcile table, Insight user order tables and 
		/// uncaches the business cache of dependent reports.
		/// </summary>
		/// <param name="connectionString">connection string pointing to the database from where
		/// the user order has to be deleted.</param>
		public void DeleteUserOrder(string connectionString)
		{			
			SqlConnection connection = new SqlConnection(connectionString);			
			SqlTransaction transaction = null;
			try
			{
				int domainUserID = ((UserContext)CachePolicy.GetCachedObject(UserContext.CacheKey)).DomainUserId;

				connection.Open();
				transaction = connection.BeginTransaction();				
				// delete user order from reconcile store
				Utility.DataAccess.ExecuteNonQuery(connection, transaction, DeleteReconcileOrderSPName, 
					Utility.DataAccess.BuildSqlParameterArray(
					OrderIDParamName, SqlDbType.VarChar, OrderID,
					DomainUserIDParamName, SqlDbType.Int, domainUserID));

				// delete the user order from insight database
				Utility.DataAccess.ExecuteNonQuery(connection, transaction, DeleteUserOrderSPName, 
					Utility.DataAccess.BuildSqlParameterArray(
					OrderIDParamName, SqlDbType.Int, OrderID,
					DomainUserIDParamName, SqlDbType.Int, domainUserID));

				transaction.Commit();
				UserOrderHelper.UnCacheDependentReports(CachePolicy);
			} 
			catch (Exception ex) 
			{
				if(transaction != null)
                    transaction.Rollback();
				throw(new Exception(@"Error in deleting user order.", ex));
			} 
			finally 
			{
				if (connection.State != ConnectionState.Closed)
					connection.Close();
			}			
		}

		#endregion

		#region Business Object Methods
		/// <summary>
		/// Initializes and loads the Datacontainer with user orders the user has access to.
		/// The method expects three parameters in the params object array in the given order:
		/// 1)Domain user ID
		/// 2)Order ID
		/// 3)Order Type ID
		/// </summary>
		/// <param name="connectionString">connection string pointing to the database to get data from</param>
		/// <param name="args">arguments to load user order</param>
		public override void Load(string connectionString, params object[] args)
		{
			if (!args.Length.Equals((int)LoadHeaderSPArguments.Count))
				throw new Exception(String.Format("User order load requires {0} parameters", 
					((int)LoadHeaderSPArguments.Count).ToString()));

			try
			{
				DataContainer = Utility.DataAccess.GetDataSet( connectionString, GetUserOrderHeaderSPName,
					Utility.DataAccess.BuildSqlParameterArray(
					DomainUserIDParamName, SqlDbType.Int, args[(int)LoadHeaderSPArguments.DomainUserID],
					OrderIDParamName, SqlDbType.Int, args[(int)LoadHeaderSPArguments.OrderID],
					OrderTypeIDParamName, SqlDbType.Int, args[(int)LoadHeaderSPArguments.OrderTypeID]));
				
                if(!DataContainer.Tables.Count.Equals(0))
					DataContainer.Tables[FirstDataTableIndex].TableName = Constants.UserOrderDetailsHeaderTableName;
				else
					throw(new Exception(@"Failed to load user order header"));
			}
			catch(Exception ex)
			{
				throw(new Exception(@"Error in loading User Order Header.", ex));
			}
		}

		/// <summary>
		/// Caches the UserOrder object to cache using the prevalent cache policy
		/// </summary>
		public override void Cache()
		{
			CachePolicy.Cache(CacheKey, this);
		}

		/// <summary>
		/// Removes the current instance from the cache using the prevalent cache policy.
		/// </summary>
		public override void UnCache()
		{
			CachePolicy.UnCache(CacheKey);
		}

		/// <summary>
		/// This method is here to override the inherited abstract method. This method has its
		/// implementation in the classes inhertining from the UserOrder class.
		/// </summary>
		/// <param name="connectionString">connection string pointing to the database to which user order
		/// header has to be added/updated</param>
		/// <param name="args">optional arguments to the save method</param>
		public override void Save(string connectionString, params object[] args )
		{
			throw new Exception (@"Save method call on user order is invalid. Please call 
				the save method on concrete user order classes");
		} 
		#endregion		

		#region Protected Methods
		/// <summary>
		/// This method adds/updates user order header to the database using the specified
		/// transaction and the sql command object. It sets the connection property of the 
		/// updateCommand object with the connection object associated with the transaction object. 
		/// It intializes the sql adapter and sets its Insert and UpdateCommand property to Insert and
		/// updateCommand object and calls the update method on the data adapter. The update method
		/// looks into the row state of the table specified and uses the appropriate command object
		/// to add/update user order inforamtion. This method is called from the save method 
		/// in the child classes to complete the operation of updating user order header to the database.
		/// </summary>
		/// <param name="transaction">specified transaction object</param>
		protected virtual void ExecuteUpdate(SqlTransaction transaction)
		{
			updateCommand.Connection = transaction.Connection;
			addCommand.Connection = transaction.Connection;

			updateCommand.Transaction = transaction;
			addCommand.Transaction = transaction;

			SqlDataAdapter adapter = new SqlDataAdapter();
			adapter.InsertCommand = addCommand;
			adapter.UpdateCommand = updateCommand;
			adapter.Update(DataContainer, Constants.UserOrderDetailsHeaderTableName);
		}		

		/// <summary>
		/// Creates the command objects for the add and update operation. This method
		/// is called from the overridden method of the child class to initialize the
		/// command objects and add parameters common to all the user order types.
		/// </summary>
		protected virtual void CreateCommands()
		{
			int domainUserID = ((UserContext)CachePolicy.GetCachedObject(UserContext.CacheKey)).DomainUserId;
			
			addCommand = new SqlCommand();
			addCommand.CommandType = CommandType.StoredProcedure;
			addCommand.CommandTimeout = Convert.ToInt32(Utility.Settings.GetKeyValue("Database.Insight.CommandTimeout", "0"));
			addCommand.Parameters.Add(DomainUserIDParamName, SqlDbType.Int).Value = domainUserID;
			addCommand.Parameters.Add(OrderIDParamName, SqlDbType.Int).SourceColumn = OrderIDColumnName;			
			addCommand.Parameters.Add(OrderTypeIDParamName, SqlDbType.Int).SourceColumn = OrderTypeIDColumnName;
			addCommand.Parameters.Add(OrderNumberParamName, SqlDbType.VarChar).SourceColumn = OrderNumberColumnName;

			string transactionDate = Convert.ToString(DataContainer.Tables[Constants.UserOrderDetailsHeaderTableName].Rows[0][TransactionDateColumnName]);
			if (transactionDate != string.Empty)
				addCommand.Parameters.Add(TransactionDateParamName, SqlDbType.DateTime).SourceColumn = TransactionDateColumnName;
			else
				addCommand.Parameters.Add(TransactionDateParamName, SqlDbType.DateTime).Value = DateTime.Now.Date;
			addCommand.Parameters.Add(TotalAmountParamName, SqlDbType.Money).Value = TotalAmount;
			addCommand.Parameters.Add(CommentParamName, SqlDbType.VarChar).SourceColumn = CommentColumnName;

            if (OrderTypeID == 4 || OrderTypeID == 5)
            {
                addCommand.Parameters.Add(InvoiceNumberParamName, SqlDbType.VarChar).SourceColumn = InvoiceNumberColumnName;
                addCommand.Parameters.Add(InvoiceDateParamName, SqlDbType.DateTime).SourceColumn = InvoiceDateColumnName;
            }
            
            addCommand.Parameters[OrderIDParamName].Direction = ParameterDirection.Output;

			updateCommand = new SqlCommand();
			updateCommand.CommandType = CommandType.StoredProcedure;
			updateCommand.CommandTimeout = Convert.ToInt32(Utility.Settings.GetKeyValue("Database.Insight.CommandTimeout", "0"));
			updateCommand.Parameters.Add(DomainUserIDParamName, SqlDbType.Int).Value = domainUserID;
			updateCommand.Parameters.Add(OrderIDParamName, SqlDbType.Int).SourceColumn = OrderIDColumnName;			
			updateCommand.Parameters.Add(OrderTypeIDParamName, SqlDbType.Int).SourceColumn = OrderTypeIDColumnName;
			updateCommand.Parameters.Add(OrderNumberParamName, SqlDbType.VarChar).SourceColumn = OrderNumberColumnName;

			if (transactionDate != string.Empty)
				updateCommand.Parameters.Add(TransactionDateParamName, SqlDbType.VarChar).SourceColumn = TransactionDateColumnName;
			else
				updateCommand.Parameters.Add(TransactionDateParamName, SqlDbType.DateTime).Value = DateTime.Now.Date;

			updateCommand.Parameters.Add(TotalAmountParamName, SqlDbType.Money).Value = TotalAmount;
			updateCommand.Parameters.Add(CommentParamName, SqlDbType.VarChar).SourceColumn = CommentColumnName;

            if (OrderTypeID == 4 || OrderTypeID == 5)
            {
                updateCommand.Parameters.Add(InvoiceNumberParamName, SqlDbType.VarChar).SourceColumn = InvoiceNumberColumnName;
                updateCommand.Parameters.Add(InvoiceDateParamName, SqlDbType.DateTime).SourceColumn = InvoiceDateColumnName;
            }
        
        }
		#endregion

		#region Private Methods
		/// <summary>
		/// Calculates the sum of lineitem amount, 
		/// to get the total amount for the user order.
		/// </summary>
		/// <returns>The sum of line item amount</returns>
		private decimal GetTotalAmount()
		{
			decimal totalAmount = 0;
			UserOrderLineItems lineItems = LineItems;
			if (lineItems != null)
			{
				foreach(UserOrderLineItem lineItem in lineItems)
				{
					totalAmount += lineItem.Amount;
				}
			}
			return totalAmount;
		}
		#endregion
	}
	#endregion
}
